﻿using System;
using System.Data;
using HsTec.Mobile.Green.Util;
using System.Web.UI.WebControls;
using System.Collections.Generic;

public partial class ReportStorage_ExportReport : System.Web.UI.Page
{
    public string sArea = String.Empty;
    public string sYear = String.Empty;
    protected void Page_Load(object sender, EventArgs e)
    {
        string sStartYears = this.Request.QueryString["sStartYears"].ToString().Trim();
        string sEndYears = this.Request.QueryString["sEndYears"].ToString().Trim();
        string sAreaID = this.Request.QueryString["sAreaID"].ToString().Trim();
        string pType = this.Request.QueryString["pType"].ToString().Trim();
        GetExportList(sStartYears, sEndYears, pType, sAreaID);
        if (sAreaID != "")
            sArea = SQLHelper.ExecuteString("select Name from Area where ID='" + sAreaID + "'");
        else
            sArea = "省";
        sYear = sStartYears + "至" + sEndYears;
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "utf-8";
        Response.AppendHeader("Content-Disposition", "attachment;filename=月度报表.xls");
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        this.RenderControl(oHtmlTextWriter);
        Response.Write(oStringWriter.ToString());
        Response.End();
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="sYears"></param>
    /// <param name="sMonths"></param>
    /// <param name="iType"></param>
    /// <param name="pType">地区汇总还是省汇总</param>
    private void GetExportList(string sStartYears, string sEndYears, string pType, string AreaID)
    {
        String sTD = String.Empty;
        //1.获取需要的数据
        DataView mydv = new DataView();
        string mysql = " SELECT RTRIM(b.name) AS colname, CASE WHEN h.id IS NOT NULL " +
                           "       THEN 'PK' ELSE '' END AS primarykey, type_name(b.xusertype) " +
                           "       + CASE WHEN b.colstat & 1 = 1 THEN '[ID(' + CONVERT(varchar, ident_seed(a.name)) " +
                           "       + ',' + CONVERT(varchar, ident_incr(a.name)) + ')]' ELSE '' END AS type, b.length, " +
                           "       CASE b.isnullable WHEN 0 THEN 'N' ELSE 'Y' END AS isnull, ISNULL(e.text, '') " +
                           "       AS [default], ISNULL(c.[value], '') AS descript " +
                           " FROM (SELECT g.id, g.colid " +
                           "         FROM sysindexes f, sysindexkeys g " +
                           "         WHERE f.id = g.id AND f.indid = g.indid AND f.indid > 0 AND f.indid < 255 AND " +
                           "               (f.status & 2048) <> 0) h RIGHT OUTER JOIN " +
                           "       syscolumns b INNER JOIN " +
                           "       sysobjects a ON b.id = a.id LEFT OUTER JOIN " +
                           "       sys.extended_properties c ON b.id = c.major_id AND b.colid = c.minor_id LEFT OUTER JOIN " +
                           "       syscomments e ON b.cdefault = e.id ON h.id = b.id AND h.colid = b.colid " +
                           " WHERE (a.id = OBJECT_ID('tb_ReportAnalysis'))" +
                           " ORDER BY b.colid";
        DataView dvField = SQLHelper.ExecuteDataView(mysql);
        string sField = String.Empty;
        for (int i = 0; i < dvField.Count; i++)
        {
            if (dvField[i]["type"].ToString() != "varchar")
            {
                if (dvField[i]["colname"].ToString() != "RA_ID" &&
                    dvField[i]["colname"].ToString() != "Area_ID" &&
                    dvField[i]["colname"].ToString() != "Years" &&
                    dvField[i]["colname"].ToString() != "AddDate" &&
                    dvField[i]["colname"].ToString() != "OperatUser" &&
                    dvField[i]["colname"].ToString() != "OperatDate" &&
                    dvField[i]["colname"].ToString() != "Remark"
                    )
                {
                    sField += sField == "" ? "sum(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"] : ",sum(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"];
                }
            }
            else
            {
                sField += sField == "" ? "max(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"] : ",max(" + dvField[i]["colname"] + ") as " + dvField[i]["colname"];
            }
        }
        if (pType == "1")
        {
            if (AreaID != "")
                mydv = SQLHelper.ExecuteDataView("select " + sField + " from tb_ReportAnalysis where years>='" + sStartYears + "' and years<='" + sEndYears + "' and Area_ID='" + AreaID + "' and Area_ID!='41'");
            else
                mydv = SQLHelper.ExecuteDataView("select " + sField + " from tb_ReportAnalysis where years>='" + sStartYears + "' and years<='" + sEndYears + "' and Area_ID!='41'");
        }
        else
        {
            mydv = SQLHelper.ExecuteDataView("select " + sField + " from tb_ReportAnalysis where years>='" + sStartYears + "' and years<='" + sEndYears + "' and Area_ID='41'");
        }
        //2.填充数值
        if (mydv.Count == 1)
        {
            DataTable dt = mydv.Table;
            for (int i = 0, j = 1; i < dt.Columns.Count - 1; i++, j++)
            {
                sTD = "<td>" + mydv[0][i] + "</td>";
                Label lbl = (Label)this.FindControl("Label" + j);
                lbl.Text = sTD;
            }
        }
        if (pType == "0")
        {
            string sPowers = String.Empty;
            sPowers = SQLHelper.ExecuteString("select sum([Power]) from rt_EnergyConsumption where years>='" + sStartYears + "' and years<='" + sEndYears + "' and Area_ID='41'");
            sPowers = String.IsNullOrEmpty(sPowers) ? "0.00" : sPowers;
            DataView dvPro = SQLHelper.ExecuteDataView("select Subentry,Proportion from tb_EnergyProportion where Area_ID='-1' and Years='" + sStartYears + "' and Subentry!='Powers'");
            Dictionary<string, string> dicList = new Dictionary<string, string>();
            dicList.Add("Powers", sPowers);
            Label powers = (Label)this.FindControl("Label1");
            powers.Text = "<td>" + sPowers + "</td>";
            for (int i = 0; i < dvPro.Count; i++)
            {
                try
                {
                    double value = double.Parse(dvPro[i]["Proportion"].ToString()) * double.Parse(dicList[dvPro[i]["Subentry"].ToString().Substring(0, dvPro[i]["Subentry"].ToString().LastIndexOf('_'))]) / 100;
                    sTD = "<td>" + value.ToString("0.00") + "</td>";
                    dicList.Add(dvPro[i]["Subentry"].ToString(), value.ToString("0.00"));
                }
                catch
                {
                    sTD = "<td>0.00</td>";
                }
                int j = i + 2;
                Label lbl = (Label)this.FindControl("Label" + j);
                lbl.Text = sTD;
            }
        }
    }
}
